--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

--CREATE TABLE se_cli_cliassur AS (SELECT * FROM &erp.cli_cliassur WHERE 1=2);





DELETE FROM se_cli_cliassur;

 INSERT INTO se_cli_cliassur
 (SELECT id client_cod,
         '1' assregr_cod,
         0 enreg_id,
         'N' enreg_s,
         (SELECT ASSINT FROM UV_CCLIENTS_ASSINT a WHERE a.id=c.id) ass1_mt,
         (SELECT To_Date('31/12/1967','dd/mm/yyyy')+DATASSINT FROM UV_CCLIENTS_DATASSINT a WHERE a.id=c.id) ass1_dat,
         NULL validass1_dat,
         (SELECT ASSEXP FROM UV_CCLIENTS_ASSEXP a WHERE a.id=c.id) ass2_mt,
         (SELECT To_Date('31/12/1967','dd/mm/yyyy')+DATASSEXP FROM UV_CCLIENTS_DATASSEXP a WHERE a.id=c.id) ass2_dat,
         NULL validass2_dat,
         (SELECT ASSFRA FROM UV_CCLIENTS_ASSFRA a WHERE a.id=c.id) ass3_mt,
         (SELECT To_Date('31/12/1967','dd/mm/yyyy')+DATASSFRA FROM UV_CCLIENTS_DATASSFRA a WHERE a.id=c.id) ass3_dat,
         NULL validass3_dat
    FROM UV_CCLIENTS c) ;

 --UPDATE se_cli_cliassur SET ass1_mt=NULL,ass1_dat=NULL WHERE ass1_mt=0 OR ass1_mt IS NULL;
 --UPDATE se_cli_cliassur SET ass2_mt=NULL,ass2_dat=NULL WHERE ass2_mt=0 OR ass2_mt IS NULL;
 --UPDATE se_cli_cliassur SET ass3_mt=NULL,ass3_dat=NULL WHERE ass3_mt=0 OR ass3_mt IS NULL;
 --DELETE se_cli_cliassur WHERE ass1_mt IS NULL AND ass2_mt IS NULL AND ass3_mt IS NULL ;


 UPDATE se_cli_cliassur tf SET enreg_id=(select enreg_id FROM &erp.cli_cliassur f WHERE f.client_cod=tf.client_cod and f.assregr_cod=tf.assregr_cod) ;
 UPDATE se_cli_cliassur tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 CALL uvse_synchroverserp('client_cod@assregr_cod','CLI_CLIASSUR','O') ;

 commit;
 
 select * from (
SELECT &outils.stragg(source) source,client_cod, assregr_cod, enreg_id, enreg_s, ass1_mt, ass1_dat, validass1_dat, ass2_mt, ass2_dat, validass2_dat, ass3_mt, ass3_dat, validass3_dat
FROM (
  SELECT 'uv' source ,g.* FROM se_cli_cliassur g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.cli_cliassur g
  )
GROUP BY client_cod, assregr_cod, enreg_id, enreg_s, ass1_mt, ass1_dat, validass1_dat, ass2_mt, ass2_dat, validass2_dat, ass3_mt, ass3_dat, validass3_dat
HAVING Count(1)!=2) r
ORDER BY client_cod,assregr_cod;